﻿using GenericParsing;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CVSToExcel.Utils
{
   public class SqlUtil
    {
       private FileItem _fileItem;

       public InstallResult AddToSql(FileItem fileItem)
       {
           _fileItem = fileItem;
           InstallResult result = new InstallResult();
           if (TableExists)
           {
               result.Success = true;
               result.SetMessage("The table already exists.  No data was imported");
               return result;
           }

           //var dataTable = BuildDataTable(result);
           var dataTable = BuildDataTable_NewWay(result);

            MakeTable(result, dataTable);

           InsertData(result,dataTable);

           return result;
       }

       public DataTable BuildDataTable(InstallResult result)
       {
           string fileName = _fileItem.FullFileName;
           OleDbConnection conn = null;
           DataSet ds = null;
           try
           {
               conn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " +
                   Path.GetDirectoryName(_fileItem.FullFileName) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited(,)\"");

               conn.Open();

               OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [" + Path.GetFileName(fileName) + "]", conn);

               ds = new DataSet("Temp");
               adapter.Fill(ds);

               if (ds.Tables.Count > 0)
               {
                   ds.Tables[0].TableName = _fileItem.TabName;
               }
           }
           finally
           {
               if (conn != null) conn.Close();
           }

           return ds.Tables.Count > 0 ? ds.Tables[0] : null;
       }

        public DataTable BuildDataTable_NewWay(InstallResult result)
        {
            DataTable dt = null;
            using (GenericParserAdapter gp = new GenericParserAdapter(_fileItem.FullFileName))
            {
                gp.FirstRowHasHeader = true;
                dt = gp.GetDataTable();
            }
            dt.TableName = _fileItem.TabName;
            return dt;
        }

       public bool TableExists = false;

       public void MakeTable(InstallResult result, DataTable dataTable)
       {
           string table = "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + dataTable.TableName + "]') AND type in (N'U'))";
           table += "BEGIN ";
           table += "create table [" + dataTable.TableName + "]";
           table += "(";
           for (int i = 0; i < dataTable.Columns.Count; i++)
           {
               if (i != dataTable.Columns.Count - 1)
                   table += "[" + dataTable.Columns[i].ColumnName + "] " + "varchar(max)" + ",";
               else
                   table += "[" + dataTable.Columns[i].ColumnName + "] " + "varchar(max)";
           }
           table += ") ";
           table += "END";

           using (SqlConnection conn = new SqlConnection(Utils.DbUtil.ConnectionString.ConnectionString))
           {
               conn.Open();
               SqlCommand cmd = new SqlCommand(table, conn);
               cmd.ExecuteNonQuery();
               conn.Close();
           }
       }

       public void InsertData(InstallResult result, DataTable dataTable)
       {
           using (SqlConnection connection = new SqlConnection(DbUtil.ConnectionString.ConnectionString))
           {
               SqlBulkCopy bulkCopy =
                   new SqlBulkCopy
                   (
                   connection,
                   SqlBulkCopyOptions.TableLock |
                   SqlBulkCopyOptions.FireTriggers |
                   SqlBulkCopyOptions.UseInternalTransaction,
                   null
                   );

               bulkCopy.DestinationTableName = "[" + dataTable.TableName + "]";
               connection.Open();

               bulkCopy.WriteToServer(dataTable);
               connection.Close();
           }
       }
    }
}
